import com.alibaba.druid.pool.DruidDataSource;
import org.junit.Test;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class Jdbc {
    private static DruidDataSource druidDataSource=null;
    private static String url;
    private static String username;
    private static String password;
    static {
        try {
            //读取文件
            Properties properties = new Properties();
            InputStream aClass = Class.forName("Jdbc").getResourceAsStream("jdbc.properties");
            properties.load(aClass);
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            //
            druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(url);
            druidDataSource.setUsername(username);
            druidDataSource.setPassword(password);
            druidDataSource.setInitialSize(10);
            druidDataSource.setMaxActive(10);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        try (Connection connection= Jdbc.getCon()){
            Statement statement = connection.createStatement();
/*            //系统输入
            Scanner scanner = new Scanner(System.in);
            System.out.print("输入用户名：");
            String userName = scanner.nextLine();
            System.out.print("输入密码：");
            String userPassword = scanner.nextLine();
            //将输入的数据写入数据库
            String write = "insert into user values(null,'" + userName + "','" + userPassword + "')";
            //执行数据库指令
            statement.executeUpdate(write);*/
            ResultSet set = statement.executeQuery("select * from emp");
            //遍历
            while (set.next()){
                System.out.println(set.getString(4));
            }
            System.out.println("输入完成");
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
    private static Connection getCon() {
        Connection connection = null;
        try {
            System.out.println(druidDataSource.getConnection());
            connection = DriverManager.getConnection(url,username ,password );
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
    @Test
    public void test(){
        try (Connection connection= Jdbc.getCon()){
            //SQL语句
            String login = "insert into user values(null,?,?)";
            String search = "select id from user where username=? and password=?";
            //预编译,锁死逻辑后替换?,防止SQL注入漏洞,Statement.RETURN_GENERATED_KEYS返回值增加一个当前自增数据的编号,next()获取
            PreparedStatement preparedStatement = connection.prepareStatement(search,Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1,"saber");
            preparedStatement.setString(2,"996");
            //执行
            ResultSet resultSet = preparedStatement.executeQuery();
            //取得key值
            resultSet.next();
            int keysInt = resultSet.getInt(1);
            System.out.println(keysInt);
            //判断
            if(resultSet.next()){
                System.out.println("成功");
            }else {
                System.out.println("失败");
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
    //批量执行
    @Test
    public void test1(){
        try (Connection connection= Jdbc.getCon()){
            Statement statement = connection.createStatement();
            //SQL语句
            String login1 = "insert into user values(null,'Lancer','222')";
            String login2 = "insert into user values(null,'Magic','2333')";
            String login3 = "insert into user values(null,'Archer','66')";
            //预编译,锁死逻辑后替换?,防止SQL注入漏洞
            statement.addBatch(login1);
            statement.addBatch(login2);
            statement.addBatch(login3);
            //执行
            statement.executeBatch();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
    //元数据
    @Test
    public void test2(){
        try (Connection connection= Jdbc.getCon()){
            DatabaseMetaData metaData = connection.getMetaData();
            //数据库相关的参数
            metaData.getURL();
            metaData.getDatabaseProductName();
            metaData.getDriverName();
            String sql="select * from emp";
            Statement statement = connection.createStatement();
            ResultSet i = statement.executeQuery(sql);
            ResultSetMetaData metaData1 = i.getMetaData();
            //表字段计数,读取数据类型
            int columnCount = metaData1.getColumnCount();
            for (int j = 0; j <columnCount ; j++) {
                metaData1.getColumnName(j);
                metaData1.getColumnTypeName(j);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
    }
}
